﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.Linq;

using ECCIT;
using ECCIT.Data;
using ECCIT.Business.DAL;
using OgilvyOne.Model;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.UKNow.Globalization;
using OgilvyOne.UKNow.Utility;
using OgilvyOne.UKNow.Model;
using OgilvyOne.UKNow.IDAL;
using SortOrder = ECCIT.SortOrder;

namespace OgilvyOne.UKNow.DAL
{
    public class MemberDAL : UKNowBaseDAL, IDALMember
    {
        private const string TBL_NAME = "UKNOW_MEMBER_INFO";
        private const string COL_MEMBER_ID = "MEMBER_ID";
        private const string COL_USERNAME = "USERNAME";
        private const string COL_PASSWORD = "PASSWORD";
        private const string COL_MEMBER_NAME = "MEMBER_NAME";
        private const string COL_USER_TYPE = "USER_TYPE";
        private const string COL_MOBLIE = "MOBLIE";
        private const string COL_LAST_LOGIN_TIME = "LAST_LOGIN_TIME";
        private const string COL_REGISTERTYPE = "REGISTERTYPE";
        private const string COL_STATE = "STATE";
        private const string COL_EMAIL = "EMAIL";
        private const string COL_BIRTHDAY = "BIRTHDAY";
        private const string COL_STUDYABROAD = "STUDYABROAD";
        private const string COL_IELTS = "IELTS";
        private const string COL_ARTSANDCULTURE = "ARTSANDCULTURE";
        private const string COL_SINA = "SINA";
        private const string COL_SOHU = "SOHU";
        private const string COL_TENGXUN = "TENGXUN";
        private const string COL_SINAWEIBO = "SINAWEIBO";
        private const string COL_TENGXUNWEIBO = "TENGXUNWEIBO";
        private const string COL_KAIXIN = "KAIXIN";
        private const string COL_DOUBAN = "DOUBAN";
        private const string COL_RENREN = "RENREN";
        private const string COL_GOOGLE = "GOOGLE";
        private const string COL_BAIDU = "BAIDU";
        private const string COL_BING = "BING";
        private const string COL_OFFLINEEVENTS = "OFFLINEEVENTS";
        private const string COL_NEWSPAPER = "NEWSPAPER";
        private const string COL_OUTDOOR = "OUTDOOR";
        private const string COL_TAXI = "TAXI";
        private const string COL_METRO = "METRO";
        private const string COL_BBS = "BBS";
        private const string COL_TV = "TV";
        private const string COL_RECOMMENDATION = "RECOMMENDATION";
        private const string COL_OTHERCHANNELS = "OTHERCHANNELS";
        private const string COL_MEMBER_HEARD_IMG = "MEMBER_HERD_IMG";
        private const string MEMBER_INSERT_TIME = "MEMBER_INSERT_TIME";

        private const string COL_REMINDER_BY_EMAIL = "REMINDER_BY_EMAIL";
        private const string COL_REMINDER_EMAIL = "REMINDER_EMAIL";
        private const string COL_REMINDER_BY_MOBILE = "REMINDER_BY_MOBILE";
        private const string COL_REMINDER_MOBILE = "REMINDER_MOBILE";
        private const string COL_REMINDER_ALL_EVENTS = "REMINDER_ALL_EVENTS";

        private const string COL_FORGETPWRDM = "FORGETPWRDM";    //guid

        /// <summary>
        /// 2012-3-31 新加的
        /// </summary>
        private const string COL_City = "City";
        private const string COL_Provice = "Provice";
        private const string COL_MOBLIEISME = "MOBLIEISME";
        private const string COL_BIRTHDAYISME = "BIRTHDAYISME";
        private const string COL_FavoriteArtist = "FavoriteArtist";

        /// <summary>
        /// 2012-6-8 李学明 app token
        /// </summary>
        private const string COL_AppToken = "AppToken";


        public IField City
        {
            get { return FieldFactory.Create(ModelType, "City", ColumnFactory.CreateColumn(COL_City, DataType.String, 20)); }
        }
        public IField Provice
        {
            get { return FieldFactory.Create(ModelType, "Provice", ColumnFactory.CreateColumn(COL_Provice, DataType.String, 20)); }
        }
        public IField MOBLIEISME
        {
            get { return FieldFactory.Create(ModelType, "MOBLIEISME", ColumnFactory.CreateColumn(COL_MOBLIEISME, DataType.Bit)); }
        }
        public IField BIRTHDAYISME
        {
            get { return FieldFactory.Create(ModelType, "BIRTHDAYISME", ColumnFactory.CreateColumn(COL_BIRTHDAYISME, DataType.Bit)); }
        }
        public IField FavoriteArtist
        {
            get { return FieldFactory.Create(ModelType, "FavoriteArtist", ColumnFactory.CreateColumn(COL_FavoriteArtist, DataType.String, 50)); }
        }

        public IField AppToken
        {
            get { return FieldFactory.Create(ModelType, "AppToken", ColumnFactory.CreateColumn(COL_AppToken, DataType.String, 200)); }
        }







        public override string TableName { get { return TBL_NAME; } }

        public override Type ModelType { get { return typeof(MemberModel); } }

        [PrimaryKey]
        public IField ID { get { return FieldFactory.Create(ModelType, "ID", ColumnFactory.CreateColumn(COL_MEMBER_ID, DataType.Int)); } }
        public IField AccountName { get { return FieldFactory.Create(ModelType, "AccountName", ColumnFactory.CreateColumn(COL_USERNAME, DataType.String, 200)); } }
        public IField Password { get { return FieldFactory.Create(ModelType, "Password", ColumnFactory.CreateColumn(COL_PASSWORD, DataType.String, 50)); } }
        public IField Name { get { return FieldFactory.Create(ModelType, "Name", ColumnFactory.CreateColumn(COL_MEMBER_NAME, DataType.String, 50)); } }
        public IField Type { get { return FieldFactory.Create(ModelType, "Type", ColumnFactory.CreateColumn(COL_USER_TYPE, DataType.String, 20)); } }
        public IField Mobile { get { return FieldFactory.Create(ModelType, "Mobile", ColumnFactory.CreateColumn(COL_MOBLIE, DataType.String, 20)); } }
        public IField LastLoginTime { get { return FieldFactory.Create(ModelType, "LastLoginTime", ColumnFactory.CreateColumn(COL_LAST_LOGIN_TIME, DataType.DateTime)); } }
        public IField RegisterType { get { return FieldFactory.Create(ModelType, "RegisterType", ColumnFactory.CreateColumn(COL_REGISTERTYPE, DataType.String, 20)); } }
        public IField State { get { return FieldFactory.Create(ModelType, "State", ColumnFactory.CreateColumn(COL_STATE, DataType.Int)); } }
        public IField Email { get { return FieldFactory.Create(ModelType, "Email", ColumnFactory.CreateColumn(COL_EMAIL, DataType.String, 200)); } }

        public IField Birthday { get { return FieldFactory.Create(ModelType, "Birthday", ColumnFactory.CreateColumn(COL_BIRTHDAY, DataType.String, 50)); } }
        public IField StudyAbroad { get { return FieldFactory.Create(ModelType, "StudyAbroad", ColumnFactory.CreateColumn(COL_STUDYABROAD, DataType.String, 50)); } }
        public IField IELTS { get { return FieldFactory.Create(ModelType, "IELTS", ColumnFactory.CreateColumn(COL_IELTS, DataType.String, 50)); } }
        public IField ArtsAndCulture { get { return FieldFactory.Create(ModelType, "ArtsAndCulture", ColumnFactory.CreateColumn(COL_ARTSANDCULTURE, DataType.String, 50)); } }
        public IField Sina { get { return FieldFactory.Create(ModelType, "Sina", ColumnFactory.CreateColumn(COL_SINA, DataType.String, 50)); } }
        public IField Sohu { get { return FieldFactory.Create(ModelType, "Sohu", ColumnFactory.CreateColumn(COL_SOHU, DataType.String, 50)); } }
        public IField TengXun { get { return FieldFactory.Create(ModelType, "TengXun", ColumnFactory.CreateColumn(COL_TENGXUN, DataType.String, 50)); } }
        public IField SinaWeibo { get { return FieldFactory.Create(ModelType, "SinaWeibo", ColumnFactory.CreateColumn(COL_SINAWEIBO, DataType.String, 50)); } }
        public IField TengXunWeibo { get { return FieldFactory.Create(ModelType, "TengXunWeibo", ColumnFactory.CreateColumn(COL_TENGXUNWEIBO, DataType.String, 50)); } }
        public IField KaiXin { get { return FieldFactory.Create(ModelType, "KaiXin", ColumnFactory.CreateColumn(COL_KAIXIN, DataType.String, 50)); } }
        public IField DouBan { get { return FieldFactory.Create(ModelType, "DouBan", ColumnFactory.CreateColumn(COL_DOUBAN, DataType.String, 50)); } }
        public IField RenRen { get { return FieldFactory.Create(ModelType, "RenRen", ColumnFactory.CreateColumn(COL_RENREN, DataType.String, 50)); } }
        public IField Google { get { return FieldFactory.Create(ModelType, "Google", ColumnFactory.CreateColumn(COL_GOOGLE, DataType.String, 50)); } }

        public IField Baidu { get { return FieldFactory.Create(ModelType, "Baidu", ColumnFactory.CreateColumn(COL_BAIDU, DataType.String, 50)); } }
        public IField Bing { get { return FieldFactory.Create(ModelType, "Bing", ColumnFactory.CreateColumn(COL_BING, DataType.String, 50)); } }
        public IField OfflineEvents { get { return FieldFactory.Create(ModelType, "OfflineEvents", ColumnFactory.CreateColumn(COL_OFFLINEEVENTS, DataType.String, 50)); } }
        public IField NewsPaper { get { return FieldFactory.Create(ModelType, "NewsPaper", ColumnFactory.CreateColumn(COL_NEWSPAPER, DataType.String, 50)); } }
        public IField Outdoor { get { return FieldFactory.Create(ModelType, "Outdoor", ColumnFactory.CreateColumn(COL_OUTDOOR, DataType.String, 50)); } }
        public IField Taxi { get { return FieldFactory.Create(ModelType, "Taxi", ColumnFactory.CreateColumn(COL_TAXI, DataType.String, 50)); } }
        public IField Metro { get { return FieldFactory.Create(ModelType, "Metro", ColumnFactory.CreateColumn(COL_METRO, DataType.String, 50)); } }
        public IField BBS { get { return FieldFactory.Create(ModelType, "BBS", ColumnFactory.CreateColumn(COL_BBS, DataType.String, 50)); } }
        public IField TV { get { return FieldFactory.Create(ModelType, "TV", ColumnFactory.CreateColumn(COL_TV, DataType.String, 50)); } }
        public IField Recommendation { get { return FieldFactory.Create(ModelType, "Recommendation", ColumnFactory.CreateColumn(COL_RECOMMENDATION, DataType.String, 50)); } }
        public IField OtherChannels { get { return FieldFactory.Create(ModelType, "OtherChannels", ColumnFactory.CreateColumn(COL_OTHERCHANNELS, DataType.String, 50)); } }

        public IField MemberImg { get { return FieldFactory.Create(ModelType, "MemberImg", ColumnFactory.CreateColumn(COL_MEMBER_HEARD_IMG, DataType.String, 200)); } }
        public IField InsertTime { get { return FieldFactory.Create(ModelType, "InsertTime", ColumnFactory.CreateColumn(MEMBER_INSERT_TIME, DataType.DateTime)); } }

        public IField ReminderByEmail { get { return FieldFactory.Create(ModelType, "ReminderByEmail", ColumnFactory.CreateColumn(COL_REMINDER_BY_EMAIL, DataType.Bit)); } }
        public IField ReminderEmail { get { return FieldFactory.Create(ModelType, "ReminderEmail", ColumnFactory.CreateColumn(COL_REMINDER_EMAIL, DataType.String, 100)); } }
        public IField ReminderByMobile { get { return FieldFactory.Create(ModelType, "ReminderByMobile", ColumnFactory.CreateColumn(COL_REMINDER_BY_MOBILE, DataType.Bit)); } }
        public IField ReminderMobile { get { return FieldFactory.Create(ModelType, "ReminderMobile", ColumnFactory.CreateColumn(COL_REMINDER_MOBILE, DataType.String, 20)); } }
        public IField ReminderAllEvents { get { return FieldFactory.Create(ModelType, "ReminderAllEvents", ColumnFactory.CreateColumn(COL_REMINDER_ALL_EVENTS, DataType.Bit)); } }

        public IField ForgetPwrdM { get { return FieldFactory.Create(ModelType, "ForgetPwrdM", ColumnFactory.CreateColumn(COL_FORGETPWRDM, DataType.String, 50)); } }

        public override IField[] InsertFields { get { return new IField[] { AccountName, Password, Name, Type, Mobile, LastLoginTime, RegisterType, State, Email, Birthday, StudyAbroad, IELTS, ArtsAndCulture, Sina, Sohu, TengXun, SinaWeibo, TengXunWeibo, KaiXin, DouBan, RenRen, Google, Baidu, Bing, OfflineEvents, NewsPaper, Outdoor, Taxi, Metro, BBS, TV, Recommendation, OtherChannels, MemberImg, ReminderByEmail, ReminderEmail, ReminderByMobile, ReminderMobile, ReminderAllEvents, ForgetPwrdM, City, Provice, MOBLIEISME, BIRTHDAYISME, FavoriteArtist,AppToken }; } }
        public override IField[] SelectFields { get { return new IField[] { ID, AccountName, Password, Name, Type, Mobile, LastLoginTime, RegisterType, State, Email, Birthday, StudyAbroad, IELTS, ArtsAndCulture, Sina, Sohu, TengXun, SinaWeibo, TengXunWeibo, KaiXin, DouBan, RenRen, Google, Baidu, Bing, OfflineEvents, NewsPaper, Outdoor, Taxi, Metro, BBS, TV, Recommendation, OtherChannels, MemberImg, ForgetPwrdM, City, Provice, MOBLIEISME, BIRTHDAYISME, FavoriteArtist, AppToken }; } }
        public override IField[] UpdateFields { get { return new IField[] { AccountName, Password, Name, Type, Mobile, LastLoginTime, RegisterType, State, Email, Birthday, StudyAbroad, IELTS, ArtsAndCulture, Sina, Sohu, TengXun, SinaWeibo, TengXunWeibo, KaiXin, DouBan, RenRen, Google, Baidu, Bing, OfflineEvents, NewsPaper, Outdoor, Taxi, Metro, BBS, TV, Recommendation, OtherChannels, MemberImg, ReminderByEmail, ReminderEmail, ReminderByMobile, ReminderMobile, ReminderAllEvents, ForgetPwrdM, City, Provice, MOBLIEISME, BIRTHDAYISME, FavoriteArtist }; } }//return InsertFields;

        public MemberDAL() { }

        public MemberDAL(LanguageType languageType) { this.Language = LanguageFactory.Create(languageType); }

        #region IDALMember Members

        public MemberModel GetMemberModelByID(int? id)
        {
            MemberModel model = (MemberModel)GetModelByPrimaryKey(this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(this.ID, id) });
            return model;
        }

        public IList<MemberModel> GetAllMemberModels()
        {
            return GetModels(null, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Name.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }
        /* 添加人;陈鹏
         * 内容：用于验证用户名 和邮件的重复
         */
        public IList<MemberModel> Check( string username)
        {
            string whereClause = string.Format("where {0}='{1}' ",  COL_MEMBER_NAME, username);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public IList<MemberModel> CheckEmail(string email)
        {
            string whereClause = string.Format("where {0}='{1}' ", COL_EMAIL, email);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public IList<MemberModel> CheckGuid(string gid)
        {
            string whereClause = string.Format("where {0}='{1}' ", COL_FORGETPWRDM, gid);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public IList<MemberModel> QueryTime(string staTime,string endTime)
        {
            string whereClause = string.Format("where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0", staTime, endTime);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(InsertTime.PropertyName, SortOrder.Asc))).List.Cast<MemberModel>().ToList();
        }

        public ResultInfo SignIn(string memberEmail, string password)
        {
            ResultInfo result = new ResultInfo(false);

            IDataParameter[] parms = DBHelper.GetDataParameters(new IField[] { Email, Password });
            parms[0].Value = memberEmail;
            parms[1].Value = password;

            string whereClause = " WHERE  EMAIL=#EMAIL AND PASSWORD=#PASSWORD ";
            try
            {
                MemberModel modelInfo = (MemberModel)GetModelInfo(whereClause, parms);
                if (modelInfo == null)
                {
                    result.Result = false;
                }
                else
                {
                    result.Result = true;
                    result.Message = Language.GetAlertMessage(ModelType, Common.Enums.Action.SignIn, Common.Enums.ActionResult.Success);
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Message = Language.GetAlertMessage(ModelType, Common.Enums.Action.SignIn, Common.Enums.ActionResult.Exception);
            }

            return result;
        }

        public ResultInfo Save(MemberModel modelInfo)
        {
            if (modelInfo == null) throw new ArgumentNullException("modelInfo");

            ResultInfo result = new ResultInfo(false);

            try
            {
                int rowsAffected = 0;
                if (modelInfo.ID == null)   // insert
                {
                    //rowsAffected = Insert(modelInfo, null);
                    string cmdInsert = DBHelper.GetInsertCommandText(TBL_NAME, InsertFields) + "; select @@identity";
                    IDataParameter[] parms = DBHelper.GetDataParameters(InsertFields);
                    DBHelper.BindDataParametersValue(parms, InsertFields, modelInfo);
                    object o = DBHelper.ExecuteScalar(cmdInsert, parms);
                    if (o != null && o != DBNull.Value)
                        modelInfo.ID = Convert.ToInt32(o);
                    //
                    rowsAffected = 1;
                }
                else // update
                {
                    rowsAffected = UpdateByPrimaryKey(modelInfo, null);
                   
                }
                //问题出在这里，判断条件错了,已更改为在插入操作时值变为1
                if (rowsAffected > 0)
                {
                    result.Message = "会员数据保存成功！";
                    result.Result = true;
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = string.Format("保存会员数据时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo Delete(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                try
                {
                    foreach (int? id in idList)
                        DeleteModelByPrimaryKey(trans, this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(this.ID, id) });

                    trans.Commit();
                    result.Result = true;
                    result.Message = "会员数据删除成功！";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    result.Message = string.Format("删除会员数据时发生异常。异常信息：{0}", ex.Message);
                }
                finally
                {
                    trans.Dispose();
                }
            }

            return result;
        }

        public ResultInfo SetReminderAllEvents(int? memberID, bool enabled)
        {
            return null;
        }

        /// <summary>
        /// My profile 
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        public DataTable GetMyProfile(int memberid,string begintime,string endtime)
        {
            var strsql = new StringBuilder();
            strsql.Append(
                @"select 
                    MOBLIE,
                    EMAIL,
                    BIRTHDAY,
                    MEMBER_ID,
                    (SELECT COUNT(1)
                     from UKNOW_MEMBER_EVENTS_MAPPING as a 
                    inner join UKNOW_EVENTS as b on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID
                    where a.MEMBER_ID=@MEMBER_ID AND A.COL_LANG_ID=@COL_LANG_ID) as favevents,
                    (select COUNT(1) from UKNOW_EVENTS_COMMENT where COL_MEMBER_ID=@MEMBER_ID and COL_LANG_ID=@COL_LANG_ID) as Commentcount
                 from 
                 dbo.UKNOW_MEMBER_INFO where MEMBER_ID=@MEMBER_ID;
                 ");
            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
					new SqlParameter("@begintime", SqlDbType.DateTime),
					new SqlParameter("@endtime", SqlDbType.DateTime),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            //下面的这两个暂时不用
            parameters[1].Value = begintime;
            parameters[2].Value = endtime;
            parameters[3].Value = (int)this.Language.LanguageType;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);         
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return new DataTable();
        }

        /// <summary>
        /// My profile Interests 所有兴趣
        /// </summary>
        /// <param name="memberid"></param>
        /// <returns></returns>
        public DataTable GetMyInterests(int memberid)
        {
             var strsql = new StringBuilder();
            strsql.Append(
                @" select 
                    isnull(b.COL_MEMBER_ID,0) as COL_MEMBER_ID,
                    a.COL_FORM_NAME,
                    a.COL_FORM_ID
                    from UKNOW_ART_FORM as a 
                    left join UKNOW_MEMBER_INTERESTS_MAPPING  as b on a.COL_FORM_ID=b.COL_INTERESTS_ID and b.COL_MEMBER_ID=@memberid
                    where a.COL_LANG_ID=@COL_LANG_ID");

            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            parameters[1].Value = (int)this.Language.LanguageType;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return new DataTable();
        }


        /// <summary>
        /// 取My Account 左边的信息
        /// </summary>
        /// <param name="memberid"></param>
        /// <returns></returns>
        public DataTable GetMyAccount(int memberid)
        {
            var strsql = new StringBuilder();
            strsql.Append(
                @"select 
	            a.MEMBER_NAME,
	            a.MEMBER_HERD_IMG,
	            a.City,
	            a.Provice,
	            b.cnname as cnProvice,
	            b.enname as enProvice,
	            c.cnname as cnCity,
	            c.enname as enCity,
	            (select COUNT(1) from UKNOW_MEMBER_EVENTS_MAPPING where COL_LANG_ID=@COL_LANG_ID and MEMBER_ID=@memberid) as favoritecount,
	            (select COUNT(1) from UKNOW_EVENTS_COMMENT where COL_LANG_ID=@COL_LANG_ID and COL_MEMBER_ID=@memberid) as Commentscount,
                a.BIRTHDAY,
                a.EMAIL,
                a.MOBLIE,
                a.FavoriteArtist  
            from dbo.UKNOW_MEMBER_INFO as a 
            left join dbo.CMS_DICTIONARY as b on b.CID=a.Provice
            left join dbo.CMS_DICTIONARY as c on c.CID=a.City
            where MEMBER_ID=@memberid");

            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            parameters[1].Value = (int)this.Language.LanguageType;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0];
            else
                return new DataTable();
        }

        /// <summary>
        /// 注销会员
        /// </summary>
        /// <param name="cnContent">中文内容</param>
        /// <param name="enContent">英文内容</param>
        /// <param name="memberid">会员ID</param>
        /// <returns></returns>
        public ResultInfo CancelMember(string cnContent,string enContent,int memberid)
        {
            ResultInfo result = new ResultInfo(false);
            //var arr = new ArrayList();
            //arr.Add("update UKNOW_MEMBER_INFO set STATE=-1 where MEMBER_ID=" + memberid);
            //arr.Add("update UKNOW_EVENTS_COMMENT set COL_COMMENT_TEXT='" + cnContent +
            //        "' where COL_LANG_ID=2052 and COL_MEMBER_ID=" + memberid);
            //arr.Add(" update UKNOW_EVENTS_COMMENT set COL_COMMENT_TEXT='" + enContent +
            //        "' where COL_LANG_ID=1033 and COL_MEMBER_ID=" + memberid);




            string sql1 =
                @"update UKNOW_MEMBER_INFO set STATE=-1 where MEMBER_ID=@memberid";
            string sql2 =
                " update UKNOW_EVENTS_COMMENT set COL_COMMENT_TEXT=@cn_content where COL_LANG_ID=2052 and COL_MEMBER_ID=@memberid";
                      string sql3="update UKNOW_EVENTS_COMMENT set COL_COMMENT_TEXT=@en_content where COL_LANG_ID=1033 and COL_MEMBER_ID=@memberid";

            IDataParameter[] parameters = {
                    new SqlParameter("@memberid", SqlDbType.Int,4),
                    new SqlParameter("@cn_content", SqlDbType.VarChar,500),
                     new SqlParameter("@en_content", SqlDbType.VarChar,500),
                    };
            parameters[0].Value = memberid;
            parameters[1].Value = cnContent;
            parameters[2].Value = enContent;
            if(DBHelper.ExecuteNonQuery(CommandType.Text,sql1,parameters)>0)
            {
                DBHelper.ExecuteNonQuery(CommandType.Text, sql2, parameters);
                DBHelper.ExecuteNonQuery(CommandType.Text, sql3, parameters);
                result.Result = true;
                result.Message = "Success";
            }
            return result;

        }

        /// <summary>
        /// 验证用户名字是否存在 true 存在 false 不存在
        /// </summary>
        /// <param name="username"></param>
        /// <param name="inputname"></param>
        /// <returns></returns>
        public bool CheckUser(string username,string inputname)
        {
            string sql = "select COUNT(1) from dbo.UKNOW_MEMBER_INFO where MEMBER_NAME!=@username and MEMBER_NAME=@inputname";
            IDataParameter[] parameters = {
                    new SqlParameter("@username", SqlDbType.NVarChar,50),
                    new SqlParameter("@inputname", SqlDbType.NVarChar,50)
                    };
            parameters[0].Value = username;
            parameters[1].Value = inputname;
            using (IDataReader reader = DBHelper.ExecuteReader(sql,parameters))
            {
                while (reader.Read())
                {
                    return Convert.ToInt32(reader[0]) > 0;
                }
            }
            return false;

        }

        /// <summary>
        /// 根据用户ID，获取用户的评论数，上传数，收藏数，赞数
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="lanagerid"></param>
        /// <returns></returns>
        public MemberExt GetUserEventCount(int memberid,int lanagerid)
        {
            var sql =
                new StringBuilder(
                    @"select CommentCount,UpCount,FavoriteCount,UploadImgCount from (
                   (select COUNT(1) as CommentCount,1 as flag from dbo.UKNOW_EVENTS_COMMENT where COL_MEMBER_ID=@memberid and COL_LANG_ID=@COL_LANG_ID
                   ) as a
                  left join (
                  select  COUNT(1) as UpCount,1 as flag from dbo.UKNOW_MEMBER_PraiseEVENTS where COL_LANG_ID=@COL_LANG_ID and MEMBER_ID=@memberid
                   ) as b  on a.flag=b.flag
                   left join (
   
                  select COUNT(1) as FavoriteCount,1 as flag from UKNOW_MEMBER_EVENTS_MAPPING where COL_LANG_ID=@COL_LANG_ID and MEMBER_ID=@memberid
                  ) as c on a.flag=c.flag
                  left join (
                  select COUNT(1) as UploadImgCount,1 as flag from UKNOW_EVENTS_COMMENT where COL_MEMBER_ID=@memberid and COL_LANG_ID=@COL_LANG_ID and datalength(COL_COMMENT_IMG_NAME)>0
                  ) as d on d.flag=a.flag
                  )");
            IDataParameter[] parameters = {
                    new SqlParameter("@memberid", SqlDbType.Int),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int)
                    };
            parameters[0].Value = memberid;
            parameters[1].Value = lanagerid;
            var ds = DBHelper.ExecuteDataSet(CommandType.Text, sql.ToString(), parameters);
            return ds.Tables[0].ToList<MemberExt>().First();
        }

        /// <summary>
        /// 更新会员头像接口用 4-29 22:55  
        /// </summary>
        /// <param name="model"></param>
        /// <param name="flag">true 更新头像 false 更新密码 </param>
        /// <returns></returns>
        public bool UpdateMemberImg(MemberModel model,bool flag)
        {

            string sql = flag ? "update UKNOW_MEMBER_INFO set MEMBER_HERD_IMG=@headimg where MEMBER_ID=@memberid" : "update UKNOW_MEMBER_INFO set PASSWORD=@headimg where MEMBER_ID=@memberid";
            
            IDataParameter[] parameters = {
                    new SqlParameter("@headimg", SqlDbType.NVarChar),
                    new SqlParameter("@memberid", SqlDbType.Int)
                    };
            parameters[0].Value = flag ? model.MemberImg : model.Password;
            parameters[1].Value = model.ID;

            return DBHelper.ExecuteNonQuery(sql, parameters) > 0;
        }

      /// <summary>
      /// 更新token
      /// </summary>
      /// <param name="email"></param>
      /// <param name="token"></param>
      /// <returns></returns>
      public  bool  UpdateToekn(string email,string token)
      {
          var sql = "update UKNOW_MEMBER_INFO set AppToken=@token where EMAIL=@email";
          IDataParameter[] parameters = {
                    new SqlParameter("@token", SqlDbType.NVarChar),
                    new SqlParameter("@email", SqlDbType.NVarChar)
                    };
          parameters[0].Value = token;
          parameters[1].Value = email;
          return DBHelper.ExecuteNonQuery(sql, parameters) > 0;
      }

        #endregion
    }
}
